package com.leave.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.leave.entity.Apply;
import com.leave.util.JdbcUtils;

public class ApplyDao {
	private QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

	/**
	 * 假条申请
	 * 
	 * @param apply
	 * @throws SQLException
	 */
	public void add(Apply apply) throws SQLException {
		String sql = "insert into leave_apply(l_s_card,s_username,g_name,c_name,s_phone,"
				+ "l_begintime,l_endtime,l_address,l_cause) " + "values(?,?,?,?,?,?,?,?,?)";
		Object[] params = { apply.getL_s_card(), apply.getS_username(), apply.getG_name(), apply.getC_name(),
				apply.getS_phone(), apply.getL_begintime(), apply.getL_endtime(), apply.getL_address(),
				apply.getL_cause() };
		qr.update(sql, params);
	}

	/**
	 * 通过学号查询请假记录
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<Apply> show(String id) throws SQLException {
		String sql = "select * from leave_apply where l_s_card = ?";
		return qr.query(sql, new BeanListHandler<>(Apply.class), id);
	}

	/**
	 * 通过aid查询请假详情
	 * 
	 * @param aid
	 * @return
	 * @throws SQLException
	 */
	public Apply show_apply(int aid) throws SQLException {
		String sql = "select * from leave_apply where aid = ?";
		return qr.query(sql, new BeanHandler<>(Apply.class), aid);
	}

	/**
	 * 通过aid修改请假信息
	 * @param apply
	 * @throws SQLException
	 */
	public void update_apply(Apply apply) throws SQLException {
		String sql = "update leave_apply set g_name=?,c_name=?,s_phone=?,l_begintime=?,"
				+ "l_endtime=?,l_address=?,l_cause=? where aid=?";
		Object[] params = { apply.getG_name(), apply.getC_name(), apply.getS_phone(), apply.getL_begintime(),
				apply.getL_endtime(), apply.getL_address(), apply.getL_cause(), apply.getAid() };
		qr.update(sql, params);
	}
	/**
	 * 通过aid删除请假申请
	 */
	public void delete_apply(int aid) throws SQLException {
		String sql = "delete from leave_apply where aid=?";
		qr.update(sql,aid);
		
	}

	/**
	 * 通过status查询未销假
	 * 
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public List<Apply> show_cancle(int status,String l_s_card) throws SQLException {
		String sql = "select * from leave_apply where status = ? and l_s_card = ?";
		return qr.query(sql, new BeanListHandler<>(Apply.class), status,l_s_card);
	}
	
	/**
	 * 通过aid来修改status
	 * @param status
	 * @param aid
	 * @throws SQLException
	 */
	 
	public void leave_cancle(int status,int aid) throws SQLException {
		String sql = "update leave_apply set status = ? where aid=?";
		qr.update(sql, status,aid);
		
	}

}
